/*Crear  una tabla Persona con: 
*Codigo (alfanumerico de 15 caracteres)
*Nombre
*Apellido
*Telefono
*Dirección
*CI
*/
USE master
IF EXISTS (SELECT name 
FROM dbo.sysdatabases 
WHERE name = 'BDFinal_Rasguido')

DROP DATABASE BDFinal_Rasguido
GO
CREATE DATABASE BDFinal_Rasguido
GO
USE BDFinal_Rasguido
GO

CREATE TABLE Persona (
	Codigo CHAR(15) PRIMARY KEY,
	Nombre VARCHAR(100),
	Apellido VARCHAR(100),
	Telefono VARCHAR(20),
	Dirección VARCHAR(500),
	CI VARCHAR(20),
)
GO

/*Copiar los empleados de la Base de Datos Adventure Works, el codigo debe ser:
*PersonTpe – InicialApellidoPaternoInicialNombre-0000000BusinessEntityId
*Ej:
*1043	SC		Ms.	Brenda	F.	Heaney
*SC-HB-000001043
*/
USE AdventureWorks2012
GO
IF EXISTS (
    SELECT * FROM sysobjects WHERE id = object_id(N'FillZeros') 
    AND xtype IN (N'FN')
)
DROP FUNCTION dbo.FillZeros
GO

CREATE FUNCTION FillZeros(
	@number int,
	@len int
	)
	RETURNS varchar(MAX)
AS
	BEGIN
		DECLARE @str varchar(MAX)
		SET @str = CAST(@number as varchar)
		WHILE(LEN(@str)<>@len)
			BEGIN
			SET @str = '0'+@str
			END
		RETURN(@str)
	END
GO

INSERT INTO BDFinal_Rasguido.dbo.Persona SELECT PersonType+'-'+LEFT(p.LastName,1)+LEFT(p.FirstName,1)+'-'+dbo.FillZeros(p.BusinessEntityID,9) as ID
		,p.FirstName as Nombre
		,p.LastName as Apellido
		,pp.PhoneNumber as Telefono
		,a.AddressLine1 as Direccion
		,dbo.FillZeros(p.BusinessEntityID,7) as CI
	FROM Person.Person p 
	JOIN Person.[BusinessEntityAddress] ba 
	ON p.BusinessEntityID=ba.BusinessEntityID
	JOIN Person.[Address] a 
	ON ba.AddressID=a.AddressID
	JOIN Person.PersonPhone pp 
	ON pp.BusinessEntityID=p.BusinessEntityID
	WHERE pp.PhoneNumber = (SELECT TOP 1 pp1.PhoneNumber FROM Person.Person ps
			JOIN Person.PersonPhone pp1
			ON pp1.BusinessEntityID=ps.BusinessEntityID
			WHERE ps.BusinessEntityID=p.BusinessEntityID) AND
			ba.AddressID = (SELECT TOP 1 ba1.AddressID FROM Person.Person ps
			JOIN Person.[BusinessEntityAddress] ba1 
			ON ps.BusinessEntityID=ba1.BusinessEntityID
			WHERE ps.BusinessEntityID=p.BusinessEntityID)
GO

USE BDFinal_Rasguido
GO

/*Crear una Tabla Asistencia
*Id (Entero Incremental con Secuencias)
*Fecha (fecha de marcación, por defecto la fecha actual)
*Ingreso (valor logico, la primera marcación de la mañana determina la entrada)
*FechaRegular (Valor Fijo, en la entreda deberá marcar 8:00, salida 12:00, entrada 14:00 salida 18:00)
*/
CREATE SEQUENCE dbo.Ids
    AS INT
    MINVALUE 1
    START WITH 1;

CREATE TABLE Asistencia(
	Id int NOT NULL DEFAULT NEXT VALUE FOR dbo.Ids
	,CI varchar(20)
	,Fecha date DEFAULT GETDATE()
	,Ingreso bit
	,FechaRegular varchar(5)
	)
GO

/*Crear un procedimiento Almacenado que dado el CI Realice la marcación, el procedimiento deberá:
*Marcar con la fecha y hora actual
*Determinar si el empleado esta saliendo o entrando
*Deberá preveer doble marcación con un rango de 15 mins, es decir si se realiza una marcación y vuelve a intentar en menos de 15 minutos, la marcación no deberá realizarse y deberá salir un Error con el mensaje que de la asistencia ya fue marcada
*/
CREATE PROCEDURE Marcar
	@CI varchar(MAX)
AS
	DECLARE @lastIn bit,@lastFR varchar(5),@lastF date,@nextIn bit,@nextFR varchar(5)
	SELECT @lastIn=Ingreso,@lastFR = FechaRegular, @lastF=Fecha
		FROM Asistencia
		WHERE CI=@CI AND Fecha = (SELECT MAX(Fecha) FROM Asistencia WHERE CI=@CI)
	IF @lastIn IS NULL OR @lastFR IS NULL OR @lastF IS NULL
	BEGIN
		INSERT INTO Asistencia (CI,FechaRegular,Ingreso) VALUES (@CI,'8:00',1)
	END
	ELSE
	BEGIN
		IF DATEDIFF(minute,@lastF,GETDATE())<15
		BEGIN
			RAISERROR('Ya se marco',16,1)
		END
		ELSE
		BEGIN
			IF @lastIn=0
			BEGIN
				SET @nextIn=1
			END
			ELSE
			BEGIN
				SET @nextIn=0
			END
			SELECT @nextFR= CASE @lastFR 
					WHEN '8:00' THEN '12:00'
					WHEN '12:00' THEN '14:00'
					WHEN '14:00' THEN '18:00'
					WHEN '18:00' THEN '8:00' END
			INSERT INTO Asistencia (CI,FechaRegular,Ingreso) VALUES (@CI,@nextFR,@nextIn)
		END
	END


GO


	

/*Crear un trigger que determine que si una persona no marco al salir al día siguiente deberá marcarle automáticamente la salida del día anterior con la marcación actual, o de los días que no haya marcado.
*/


/*Realizar un procedimiento que muestre en un reporte la marcación en un Rango de fechas de un empleado en particular (dado el CI) y si el parámentro CI llega como NULL, deberá mostrar todos los empleados. El reporte debe ser de forma horizontal y las columnas deberá ser cada fecha (si el o los empleados no asistieron deberá mostrarse como vacio, ej. Sábado y Domingo)
*/

CREATE PROCEDURE Reporte
	@CI varchar(MAX),
	@startdate date,
	@endate date
AS
	IF @CI IS NULL
	BEGIN
		SELECT p.Nombre,p.Apellido,a.* FROM Asistencia a
		JOIN Persona p ON a.CI=p.CI
		WHERE Fecha>=@startdate and Fecha<=@endate
		ORDER BY CI,Fecha
	END
	ELSE
	BEGIN
		SELECT p.Nombre,p.Apellido,a.* FROM Asistencia a
		JOIN Persona p ON a.CI=p.CI
		WHERE CI=@CI and Fecha>=@startdate and Fecha<=@endate
		ORDER BY CI,Fecha
	END


GO


